REM  SQL scripts for Chapter 10 of ORACLE8 DBA Handbook by Kevin Loney.
REM  See www.osborne.com for updates to these scripts.


exp help=Y

exp system/manager file=expdat.dmp compress=Y owner=(HR,THUMPER)

rem
rem   user_tablespace_maps.sql
rem
rem  This script maps user objects to tablespaces.
rem
set pagesize 60
break on Owner on Tablespace
column Objects format A20
select
      Owner,
      Tablespace_Name,
      COUNT(*)||' tables' Objects
 from DBA_TABLES
group by
      Owner,
      Tablespace_Name
union
select
      Owner,
      Tablespace_Name,
      COUNT(*)||' indexes' Objects
from dba_indexes
group by
      Owner,
      Tablespace_Name

spool user_locs.lst
/
spool off


rem
rem   user_tablespace_maps.sql
rem
rem  This script maps user objects to tablespaces.
rem
set pagesize 60
break on Tablespace on Owner
column Objects format A20
select
      Tablespace_Name,
      Owner,
      COUNT(*)||' tables' Objects
from DBA_TABLES
group by
      Tablespace_Name,
      Owner
union
select
      Tablespace_Name,
      Owner,
      COUNT(*)||' indexes' Objects
from DBA_INDEXES
group by
      Tablespace_Name,
      Owner

spool ts_locs.lst
/
spool off


exp system/manager file=hr.dmp owner=HR indexes=Y compress=Y

Svrmgrl
SVRMGR> connect internal
SVRMGR> drop tablespace HR_INDEXES including contents;
SVRMGR> drop tablespace HR_TABLES including contents;
SVRMGR> create tablespace HR_TABLES...
SVRMGR> create tablespace HR_INDEXES...
SVRMGR> exit

imp system/manager file=hr.dmp full=Y buffer=64000 commit=Y



Rem
rem  third_party_grants.sql
rem
rem  This query searches for grants made by users
rem  other than the table owners.  These grants cannot
rem  be exported via User exports.
rem
break on Grantor skip 1 on Owner on Table_Name
select
      Grantor,          /*Account that made the grant*/
      Owner,            /*Account that owns the table*/
      Table_Name,       /*Name of the table*/
      Grantee,          /*Account granted access*/
      Privilege,        /*Privilege granted*/
      Grantable         /*Granted with admin option?*/
from DBA_TAB_PRIVS
order by Grantor, Owner, Table_Name, Grantee, Privilege

spool third_parts_privs.lst
/
spool off


rem
rem  third_party_indexes.sql
rem
rem  This query searches for indexes created by
rem  anyone other than the table owner.
rem
select
      Owner,                /*Owner of the index*/
      Index_Name,           /*Name of the index*/
      Table_Owner,          /*Owner of the table*/
      Table_Name            /*Name of the indexed table*/
from DBA_INDEXES
where Owner != Table_Owner

spool third_party_indexes.lst
/
spool off



exp system/manager FILE=expdat.dmp TABLES=(Thumper.SALES)


exp system/manager FILE=expdat.dmp TABLES=(Thumper.SALES:Part1)


imp system/manager file=expdat.dmp
imp system/manager file=expdat.dmp buffer=64000 commit=Y


exp system/manager file=thumper.dat owner=thumper grants=N indexes=Y compress=Y rows=Y

imp system/manager file=thumper.dat FROMUSER=thumper TOUSER=flower rows=Y indexes=Y

imp system/manager file=expdat.dmp full=Y commit=Y buffer=64000


imp system/manager file=expdat.dmp ignore=N rows=N commit=Y buffer=64000




 exp system/manager file=expdat.dmp owner=thumper

 imp system/manager file=expdat.dmp indexfile=indexes.sql full=Y
 imp system/manager file=expdat.dmp fromuser=thumper touser=flower indexes=N commit=Y buffer=64000

 sqlplus flower/password
SQL> @indexes




> tar -cvf /dev/rmt/0hc /db0[1-9]/oracle/CC1


> tar -rvf /dev/rmt/0hc /orasw/app/oracle/CC1/pfile/initcc1.ora

ORACLE_SID=cc1; export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
. oraenv
svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate;
SVRMGR> exit
insert backup commands like the "tar" commands here
svrmgrl
SVRMGR> connect internal
SVRMGR> startup



svrmgrl
SVRMGR>connect internal
SVRMGR>startup mount cc1;
SVRMGR>alter database archivelog;
SVRMGR>archive log start;
SVRMGR>alter database open;

archive log list

svrmgrl
SVRMGR>connect internal
SVRMGR>startup mount cc1;
SVRMGR>alter database noarchivelog;
SVRMGR>alter database open;


select Name,
       Value
  from V$PARAMETER
 where Name like 'log_archive%';




#
# Sample Hot Backup Script for a UNIX File System database
#
# Set up environment variables:
ORACLE_SID=cc1; export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
. oraenv
#
#   Step 1.  Perform a tablespace-by-tablespace backup
#   of the datafiles.  Set each tablespace, one at a time,
#   into begin backup state.  Then back up its datafiles
#   and return the tablespace to its normal state.
#
# Note for UNIX:  Set up an indicator for Server Manager
# (called EOFarch1 here) so that the command file will 
#  stay within Server Manager.
#
svrmgrl <<EOFarch1
connect internal
REM
REM   Back up the SYSTEM tablespace
REM
alter tablespace SYSTEM begin backup;
!tar -cvf /dev/rmt/0hc /db01/oracle/CC1/sys01.dbf
alter tablespace SYSTEM end backup;
REM
REM  The SYSTEM tablespace has now been written to a
REM   tar saveset on the tape device /dev/rmt/0hc.  The
REM   rest of the tars must use the "-rvf" clause to append
REM   to that saveset.
REM
REM   Back up the RBS tablespace
REM
alter tablespace RBS begin backup;
!tar -rvf /dev/rmt/0hc /db02/oracle/CC1/rbs01.dbf
alter tablespace RBS end backup;
REM
REM   Back up the DATA tablespace
REM   For the purposes of this example, this tablespace
REM   will contain two files, data01.dbf and data02.dbf.
REM   The * wildcard will be used in the filename.
REM
alter tablespace DATA begin backup;
!tar -rvf /dev/rmt/0hc /db03/oracle/CC1/data0*.dbf
alter tablespace DATA end backup;
REM
REM   Back up the INDEXES tablespace
REM
alter tablespace INDEXES begin backup;
!tar -rvf /dev/rmt/0hc /db04/oracle/CC1/indexes01.dbf
alter tablespace INDEXES end backup;
REM
REM   Back up the TEMP tablespace
REM
alter tablespace TEMP begin backup;
!tar -rvf /dev/rmt/0hc /db05/oracle/CC1/temp01.dbf
alter tablespace TEMP end backup;
REM
REM   Follow the same pattern to back up the rest
REM   of the tablespaces.
REM
REM
REM         Step 2.  Back up the archived redo log files.
REM
REM  First, stop the archiving process.  This will keep
REM  additional archived redo log files from being written
REM  to the destination directory during this process.
REM
archive log stop
REM
REM   Exit Server Manager, using the indicator set earlier.
exit
EOFarch1
#
#  Record which files are in the destination directory.
#     Do this by setting an environment variable that is
#  equal to the directory listing for the destination 
#  directory.
#  For this example, the log_archive_dest is 
#  /db01/oracle/arch/CC1.
#
FILES=`ls /db01/oracle/arch/CC1/arch*.dbf`; export FILES
#
#  Now go back into Server Manager and restart the
#  archiving process.  Set an indicator (called EOFarch2
#  in this example).
#
svrmgrl <<EOFarch2
connect internal
archive log start;
exit
EOFarch2
#
#  Now back up the archived redo logs to the tape
#  device via the "tar" command, then delete them
#  from the destination device via the "rm" command.
#
tar -rvf /dev/rmt/0hc $FILES
rm -f $FILES
#
#     Step 3.  Back up the control file to a disk file.
#
svrmgrl <<EOFarch3
connect internal
alter database backup controlfile to
   'db01/oracle/CC1/CC1controlfile.bck';
exit
EOFarch3
#
#  Back up the control file to the tape.
#
tar -rvf /dev/rmt/0hc /db01/oracle/CC1/CC1controlfile.bck
#
#  End of hot backup script.



select 
    'alter tablespace '||Tablespace_Name||' begin backup;'
  from DBA_TABLESPACES
 where Status <> 'INVALID'

spool alter_begin.sql
/
spool off


alter tablespace SYSTEM begin backup;
alter tablespace RBS begin backup;
alter tablespace TEMP begin backup;
alter tablespace DATA begin backup;
alter tablespace INDEXES begin backup;


tar -cvf /dev/rmt/0hc /db0[1-9]/oracle/CC1


set pagesize 0

select 
    'alter tablespace '||Tablespace_Name||' end backup;'
  from DBA_TABLESPACES
 where Status <> 'INVALID'

spool alter_end.sql
/
spool off




#     Step 1: Stop the archiving process. This will keep
#     additional archived redo log files from being written
#     to the destination directory during this process.
#
svrmgrl <<EOFarch1
connect internal
archive log stop;
REM
REM   Exit Server Manager using the indicator set earlier.
exit
EOFarch1
#
#     Step 2: Record which files are in the destination 
#  directory.
#     Do this by setting an environment variable that is
#  equal to the directory listing for the destination 
#  directory.
#  For this example, the log_archive_dest is
#  /db01/oracle/arch/CC1.
#
FILES=`ls /db01/oracle/arch/CC1/arch*.dbf`; export FILES
#
#     Step 3: Go back into Server Manager and restart the
#  archiving process. Set an indicator (called EOFarch2
#  in this example).
#
svrmgrl <<EOFarch2
connect internal
archive log start;
exit
EOFarch2
#
#     Step 4. Back up the archived redo logs to the tape
#  device via the "tar" command, then delete them
#  from the destination device via the "rm" command.
#
tar -rvf /dev/rmt/0hc $FILES
#
#     Step 5. Delete those files from the destination directory.
#
rm -f $FILES
#
#     End of archived redo log file backup script.



REM
REM   Back up the RBS tablespace - directly to tape
REM
alter tablespace RBS begin backup;
!tar -rvf /dev/rmt/0hc /db02/oracle/CC1/rbs01.dbf
alter tablespace RBS end backup;
REM


REM
REM   Back up the RBS tablespace - to another disk (UNIX)
REM
alter tablespace RBS begin backup;
!cp /db02/oracle/CC1/rbs01.dbf /db10/oracle/CC1/backups
alter tablespace RBS end backup;
REM


#
# Procedure for moving archived redo logs to another device
#
svrmgrl <<EOFarch2
connect internal
archive log stop;
!mv /db01/oracle/arch/CC1 /db10/oracle/arch/CC1
archive log start;
exit
EOFarch2
#
# end of archived redo log directory move.



alter database backup controlfile to trace;

 cp /db10/oracle/CC1/data01.dbf /db03/oracle/CC1/data01.dbf
 ORACLE_SID=cc1; export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
. oraenv
svrmgrl
SVRMGR> connect internal
SVRMGR> startup mount cc1;

 SVRMGR> recover database;
 SVRMGR> alter database open;

SVRMGR> connect internal
SVRMGR> startup mount instance_name;
SVRMGR> recover database until time '1998-11-28:14:40:00';

SVRMGR> alter database open resetlogs;
> exp system/manager file=saved.dmp tables=(owner.tablename)
> imp system/manager file=saved.dmp full=Y commit=Y buffer=64000

